-- 按人员工号同步
UPDATE s  
SET s.staff_tep_id = ve.id  
FROM [192.168.168.168].HRMS.dbo.v_employee ve  
INNER JOIN d_staff_relation s ON ve.EmpNo = s.tem_Emp_no  


-- 按人员姓名同步
UPDATE s  
SET s.staff_tep_id = ve.id  
FROM [192.168.168.168].HRMS.dbo.v_employee ve  
INNER JOIN d_staff_relation s ON ve.EmpName = s.tem_Emp_name


-- 同步房间剩余床位数和房间状态
-- 声明表变量以存储旧值和新值  
DECLARE @TempTableOldNew TABLE (aid INT, apartment_id VARCHAR(255), old_vacancy INT, new_vacancy INT, old_apartment_status INT, new_apartment_status INT);  
  
-- 首先，计算新的vacancy值，但不更新，只存储到临时表中  
INSERT INTO @TempTableOldNew (aid, apartment_id, old_vacancy, new_vacancy, old_apartment_status, new_apartment_status)  
SELECT   
		a.aid,
    a.apartment_id,  
    a.vacancy AS old_vacancy,  
    a.number_of_people - COALESCE(sr.staff_count, 0) AS new_vacancy,
		a.apartment_status as old_apartment_status,
		case when (a.number_of_people - COALESCE(sr.staff_count, 0))>0 then 1 else 0 end as new_apartment_status
FROM   
    d_apartment a  
    LEFT JOIN (  
        SELECT apartment_id, COUNT(*) AS staff_count  
        FROM d_staff_relation  
        WHERE is_deleted = 1  
        GROUP BY apartment_id  
    ) sr ON a.apartment_id = sr.apartment_id;  
  
-- 更新d_apartment表  
UPDATE a  
SET vacancy = n.new_vacancy,
		apartment_status = n.new_apartment_status
FROM d_apartment a  
JOIN @TempTableOldNew n ON a.apartment_id = n.apartment_id;  
  
	
-- 从@TempTableOldNew中选择那些vacancy发生变化的行，并插入到日志表中  
INSERT INTO d_operationLog(operation_name, operation_context, operation_date)  
SELECT 
	'定时修正房间信息',
	'房间号：'+CAST(t.apartment_id AS VARCHAR)+' 修改前剩余床位数:'+CAST(t.new_vacancy AS VARCHAR)+' 修改后:'+CAST(t.old_vacancy AS VARCHAR)+
	' 修改前宿舍状态:'+CAST(t.old_apartment_status AS VARCHAR)+' 修改后宿舍状态:'+CAST(t.new_apartment_status AS VARCHAR),
	GETDATE()
FROM @TempTableOldNew t
WHERE old_vacancy <> new_vacancy or old_apartment_status <> new_apartment_status;  

-- 从@TempTableOldNew中选择那些vacancy发生变化的行，并插入到房间备份表中  
INSERT INTO d_apartment_bak(aid,apartment_id,new_vacancy,old_vacancy,old_apartment_status,new_apartment_status,update_date,operation_type)  
SELECT 
	t.aid,
	t.apartment_id,
	t.new_vacancy,
	t.old_vacancy,
	t.old_apartment_status,
	t.new_apartment_status,
	GETDATE(),
	'定时更新宿舍信息'
FROM @TempTableOldNew t
WHERE old_vacancy <> new_vacancy or old_apartment_status <> new_apartment_status;
  
